package com.flex.mobile.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Service;

import com.flex.mobile.dao.IGenericDAO;
import com.flex.mobile.dao.model.CustInfo;

@Service("custInfoDao")
public class CustInfoDao implements IGenericDAO<CustInfo> {
	private final SimpleJdbcTemplate template;

	private final SimpleJdbcInsert insertContact;

	@Autowired
	public CustInfoDao(DataSource dataSource) {
		this.template = new SimpleJdbcTemplate(dataSource);
		this.insertContact = new SimpleJdbcInsert(dataSource).withTableName("CUST_INFO").usingGeneratedKeyColumns("ID");
	}

	private final RowMapper<CustInfo> summaryRowMapper = new ParameterizedRowMapper<CustInfo>() {

		@Override
		public CustInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
			CustInfo custInfo = new CustInfo();
			custInfo.setId(rs.getLong("ID"));
			custInfo.setTid(rs.getLong("T_ID"));
			custInfo.setUserName(rs.getString("USERNAME"));
			custInfo.setPassword(rs.getString("PASSWORD"));
			custInfo.setName(rs.getString("NAME"));
			custInfo.setNickName(rs.getString("NICKNAME"));
			custInfo.setCellPhone1(rs.getString("CELLPHONE1"));
			custInfo.setCellPhone2(rs.getString("CELLPHONE2"));
			custInfo.setCellPhone2(rs.getString("CELLPHONE3"));
			custInfo.setGender(rs.getString("GENDER"));
			custInfo.setEmail(rs.getString("EMAIL"));
			custInfo.setCountry(rs.getString("COUNTRY"));
			custInfo.setProvince(rs.getString("PROVINCE"));
			custInfo.setCity(rs.getString("CITY"));
			custInfo.setRegistDate(rs.getDate("REGISTER_DATE"));
			custInfo.setIphoneToken(rs.getString("IPHONE_TOKEN"));
			return custInfo;
		}

	};

	@Override
	public CustInfo create(CustInfo custInfo) {
		Map<String, Object> parameters = new HashMap<String, Object>();
		String sql = "select nextval('UserSeq')";
		int cid = this.template.queryForInt(sql, new Object[] {});
		custInfo.setTid(cid);
		parameters.put("ID", null);
		parameters.put("T_ID", cid);
		parameters.put("USERNAME", custInfo.getUserName());
		parameters.put("PASSWORD", custInfo.getPassword());
		parameters.put("NAME", custInfo.getName());
		parameters.put("NICKNAME", custInfo.getNickName());
		parameters.put("CELLPHONE1", custInfo.getCellPhone1());
		parameters.put("CELLPHONE2", custInfo.getCellPhone2());
		parameters.put("CELLPHONE3", custInfo.getCellPhone3());
		parameters.put("GENDER", custInfo.getGender());
		parameters.put("EMAIL", custInfo.getEmail());
		parameters.put("COUNTRY", custInfo.getCountry());
		parameters.put("PROVINCE", custInfo.getProvince());
		parameters.put("CITY", custInfo.getCity());
		Number id = this.insertContact.executeAndReturnKey(parameters);
		custInfo.setId(id.longValue());
		return custInfo;
	}

	@Override
	public List<CustInfo> findAll() {
		return this.template.query("SELECT * FROM CUST_INFO ", this.summaryRowMapper);
	}

	@Override
	public CustInfo findById(long id) {
		String sql = "select * from CUST_INFO e " + "where e.id = ? ";
		return this.template.queryForObject(sql, summaryRowMapper, id);
	}

	@Override
	public List<CustInfo> findByName(String name) {
		String sql = "select * from CUST_INFO e " + "where e.USERNAME  like ?";
		return this.template.query(sql, summaryRowMapper, "%" + name.trim() + "%");
	}
	
	public List<CustInfo> findByNameOrPhone(String userNameOrPhone) {
	   String sql = "select * from CUST_INFO e " + "where e.USERNAME  like ? or e.CELLPHONE1 = ?";
      return this.template.query(sql, summaryRowMapper, "%" + userNameOrPhone.trim() + "%", userNameOrPhone.trim());
	}

	@Override
	public boolean remove(CustInfo custInfo) {
		int count = this.template.update("DELETE FROM CUST_INFO WHERE id=?", custInfo.getId());
		return count == 1;
	}

	@Override
	public boolean update(CustInfo custInfo) {
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE CUST_INFO SET PASSWORD=?, NAME=?, NICKNAME=?, CELLPHONE1=?, CELLPHONE2=? ,");
		sql.append("CELLPHONE3=? ,GENDER =? ,EMAIL=?,COUNTRY=?,PROVINCE=?,CITY=? ");
		sql.append(" WHERE id=?");
		int count = this.template.update(sql.toString(), custInfo.getPassword(), custInfo.getName(), custInfo.getNickName(), custInfo.getCellPhone1(), custInfo.getCellPhone2(), custInfo
				.getCellPhone3(), custInfo.getGender(), custInfo.getEmail(), custInfo.getCountry(), custInfo.getProvince(), custInfo.getCity(), custInfo.getId());
		return count == 1;
	}
	
	public boolean updateIphoneTokenByTid(String token, long tid) {
      StringBuffer sql = new StringBuffer();
      sql.append("UPDATE CUST_INFO SET IPHONE_TOKEN=? WHERE id=?");
      int count = this.template.update(sql.toString(), token, tid);
      return count == 1;
   }

}
